Use Measure as a slicer without Unpivot Data

[Anonymous]

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:

Attribute	Value
% Expected Payments	1
% Expected Accounts	2

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

[amitchandak]

@Anonymous , refer this

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115

[Anonymous]

Hi @amitchandak ,

Thanks for the revert. But, I am not sure if this post is helpful in my scenerio where I want to put together the divison measure in one slicer for the graph. If I use this approach, I can have the legends of the division metrics in the graph with the metrics as slicer but won't be able to use the month as legend.

Please correct me if I am wrong?

[deepak91g · Accepted solution]

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 ( SlicerValue, 2, [%ExpectedAccounts], 1, [%ExpectedPayments], BLANK () )


Thanks

Deepak Gupta
BI Consultant @Prodata

[Anonymous]

@deepak91g , It works. Thank you so much ! 🙂

[Anonymous]

View example:

[deevaker]

Please refer to the attached file solution https://drive.google.com/open?id=1cLPKeis3HQVft1GuexRtlEjrV8_MtFlT