Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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


Thanks

Deepak Gupta
BI Consultant @Prodata

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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?

 

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

Anonymous
Not applicable

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

Anonymous
Not applicable

View example:Capture.PNG

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.