Forum Discussion

Thiyagu's avatar
Thiyagu
Helper III
8 years ago

Disable aggregate

Hi All,   I have two tabs, first tab is full of slicers and second tab works based on slicer selection in first tab.   I have some slicers in tab 1, mainly fiscal period and periodic (MTD,QTD,YTD...
Need Help
Tips and Tricks
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi Thiyagu,

 

Can you share your pbix file please? Please mask the private parts first. 

Generally, if you select 2017006, only the data of 2017006 will be showed up. Or how can we call it slicer? But we can make some changes in the model. Maybe a measure also could be helpful.

 

Best Regards,

Dale

  • Thiyagu's avatar
    Thiyagu
    Helper III
    8 years ago

    Hi v-jiascu-msft

     

    I agree with you. But users requirement like this. I cant share pbix file, but i can help you with more snip/examples if you want.

     

    My measure is like this,

    _Measure = 

    Var _curMonth= IF(Hasonefilter(fiscalperiod),values(fiscalperiod),Max(fiscalperiod))

    Var _curQtr = IF(Hasonefilter(fiscalquarter),values(fiscalquarter),Max(fiscalquarter))

    Var _curYear = IF(Hasonefilter(fiscalyear),values(fiscalyear),Max(fiscalyear))

    Var _Period = IF(Hasonefilter(periodic),values(periodic),"MTD")
    Var _Scale = IF(Hasonefilter(scale),values(scale),"Whole Number")

    Var _Value = SWITCH (
    TRUE (),
    _Period = "QTD", CALCULATE (
    SUM ( actuals),
    FILTER (
    ALL (
    table[Fiscal Period],
    table[Fiscal Quarter],
    table[Fiscal Year]
    ),
    table[Fiscal Quarter] = _curQtr
    && table[Fiscal Year] = _curYear
    && table[Fiscal Period] <= _curMonth && table[category] = "Sales"
    )
    ),
    _Period = "YTD", CALCULATE (
    SUM ( table[actuals] ),
    FILTER (
    ALL (
    table[Fiscal Period],
    table[Fiscal Quarter],
    table[Fiscal Year],table[category]
    ),
    table[Fiscal Year] = _curYear
    && table[Fiscal Period] <= _curMonth && table[category] = "Sales"
    )
    ),
    _Period = "MTD", CALCULATE (
    SUM ( table[actuals] ),
    FILTER (
    table,
    table[Fiscal Period] = _curMonth && table[category] = "Sales"
    )
    )
    )


    Return

    SWITCH (
    TRUE (),
    _Scale = "Whole Number", _Value,
    _Scale = "Thousands", DIVIDE ( _Value, 1000 ),
    _Scale = "Millions", DIVIDE ( _Value, 1000000 )
    )

    Thanks,

    Thiyaga