Skip to main content

Hello, i use Semantic layer as an endpoint to analyze the data through Microsoft Excel. In my model i have Calendar table (auto generated by TX) which dates from 2015 up to 2030. Fact in my dataset has values only up to year 2026.

When i open analysis services endpoint through Excel and want to filter values based on Calendar, i can see the empty row in filter. I assume this is due to the reason that Calendar dimension has values up until 2030, and the fact - up until 2026:

Is there anyway to not show empty values coming from dimensions (that do not have matches within the fact) in the analysis services from semantic layer?

 

Thanks!

Dear @gediminas.juskenas 

 

In the semantic layer table you can set a filter. Use a subquery to filter the calander table to only contain values which are in your fact table like so:

 

 

 

 

 

 

Hope this helps

= Daniel

Worked like a charm 🙂 Only a small thing - “WHERE” was not needed, at least on my version of TX. Just put DateValue] IN <...> and it did the job. Thanks a lot!

Thanks! I've updated the image so the next person who looks at this will have to correct image.

 

Reply


Terms and ConditionsCookie settings