Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Use Measure as a slicer without Unpivot Data

Hi everyone,

 

I have to re-create the sample view where % Expected Payments and % Expected Accounts are division calculations.

Slicer 1 : Month

Slicer 2: Division metrics - % Expected Payments and % Expected Accounts 

Slicer 3 & 4 : Company and Unit  

Graph : Line Chart with x axis as days, value as division calculations and legend as month

 

At present, I created another table like this:

AttributeValue
% Expected Payments1
% Expected Accounts2

 

In my data, I added two columns to hold this information and did unpivot.

 

I linked both the tables with the column Attribute and used the below DAX to do the calculations:

 

Measure = IF(CONTAINS(Table1,Table1[Attribute],"% Expected Payments"),DIVIDE(SUM(Table1[Payments]),SUM(Table1[Estimated Payments])),IF(CONTAINS(Table1,Table1[Attribute],"% Expected Accounts"),DIVIDE(SUM(Table1[Accounts]),SUM(Table1[Estimated Accounts])),0))

This solution works for me.

However,
I have millions of data and 30 attributes. This solution will increase my data rows to : Millions of Data * 30, which is a lot.
Every month, the data increases in size and I have to store that data in Power-BI Desktop.
Is there any way to achieve the same scenerio without doing the unpivot option in the actual data ? Maybe by creating different measure for each metric and proceeding somehow to get the correct calculation ?

Any help is appreciated.

 

Please find my sample solution of dummy data for your alteration here :
https://drive.google.com/open?id=1UydHHYd4iBVDTu7xoWfRoHM3OYC4lgQZ 


Thanks & Regards,
Sunaina Khera

  • Hi Anonymous ,


    1. Remove all table transformations from Power Query steps.

    2. Create two Measures %ExpectedAccounts  and %ExpectedAccounts

    3. Disconnected Slicer with a Measure: Create a measure that will use the concept of disconnected slicer with SWITCH() function.

    https://powerpivotpro.com/2018/05/disconnected-slicers-with-dax-variables-selectedvalues/

     

    %ExpectedAccounts =
    DIVIDE (
        SUM ( 'Table1 (2)'[Accounts] ),
        SUM ( 'Table1 (2)'[Estimated Accounts] )
    )
    %ExpectedPayments =
    DIVIDE (
        SUM ( 'Table1 (2)'[Payments] ),
        SUM ( 'Table1 (2)'[Estimated Payments] )
    )
    AmountMeasure =
    VAR SlicerValue =
        SELECTEDVALUE ( 'Attribute Mapper'[Value] )
    RETURN
        SWITCH ( SlicerValue2, [%ExpectedAccounts], 1, [%ExpectedPayments], BLANK () )


    Thanks

    Deepak Gupta
    BI Consultant @Prodata

6 Replies