Semantic layer (Analysis services) remove unused dimension values from endpoint

  • 19 September 2023
  • 3 replies

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?




Best answer by daniel 19 September 2023, 11:35

View original

3 replies

Userlevel 4
Badge +5

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!

Userlevel 4
Badge +5

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