Forum Discussion

govind_021's avatar
govind_021
Super User
1 year ago
Solved

Dax

Hi Dax experts , I have one scenerio pleae help me , if you see i have parameter and has three categories year , quarter and category  it is not always the case that thse selection will be in the ...
Need Help
  • v-saisrao-msft's avatar
    v-saisrao-msft
    1 year ago

    Hi govind_021, 
    Thank you for reaching out to the Microsoft fabric community forum. Thank you @ for your input on this issue. 
    After thoroughly reviewing the details you provided

    Dax Measure: 

    Check = 
SWITCH(
    TRUE(),
    ISINSCOPE('Date'[QuarterInCalendar]),
        CALCULATE([Total Sales], REMOVEFILTERS('Date'[QuarterInCalendar])),
    ISINSCOPE('Date'[Year]),
        CALCULATE([Total Sales], REMOVEFILTERS('Date'[Year])),
    ISINSCOPE('Product'[Category]),
        CALCULATE([Total Sales], REMOVEFILTERS('Product'[Category])),
    [Total Sales]
)

    Output: 

     


    If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly. 
     
    Thank you.

pankajnamekar25's avatar
pankajnamekar25
Super User
1 year ago

Hello govind_021 

 

Try this measure

Dynamic Lowest Level Total =

VAR VisibleColumns = SELECTCOLUMNS(VALUES('YourTable'), "Column", "YourColumnName")

VAR IsQuarterVisible = ISINSCOPE('YourTable'[Quarter])

VAR IsYearVisible = ISINSCOPE('YourTable'[Year])

VAR IsCategoryVisible = ISINSCOPE('YourTable'[Category])

 

RETURN

SWITCH(

    TRUE(),

    NOT IsQuarterVisible && NOT IsCategoryVisible,

        CALCULATE([Total Sales], REMOVEFILTERS('YourTable'[Year])),

   

    NOT IsQuarterVisible && NOT IsYearVisible,

        CALCULATE([Total Sales], REMOVEFILTERS('YourTable'[Category])),

   

    NOT IsYearVisible && NOT IsCategoryVisible,

        CALCULATE([Total Sales], REMOVEFILTERS('YourTable'[Quarter])),

 

    NOT IsQuarterVisible,

        CALCULATE([Total Sales], REMOVEFILTERS('YourTable'[Quarter])),

 

    NOT IsYearVisible,

        CALCULATE([Total Sales], REMOVEFILTERS('YourTable'[Year])),

 

    NOT IsCategoryVisible,

        CALCULATE([Total Sales], REMOVEFILTERS('YourTable'[Category])),

 

    -- If all are visible, assume the last is the most granular

    CALCULATE([Total Sales])

)

 

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.