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
Long
Frequent Visitor

Relations between tables with measure column

Hi everyone,

 

Could you help to understand the following behavior and how to deal with this:

I have a simple structure with one fact table and two dimensions with both direction relations:

 

Screenshot_1.png

And I have a report with two slicers from dimension tables (State Name and Client Name), and one slicer for test - ClientKey directly from the Fact table: 

It is filtered by the 'Client Name' dimension slicer with Client = 5

 

Screenshot_2.png

 

When I added a simple measure to the fact table, for example M_Value = 10, the table is not filtered by StateKey, but Client Name slicer is still 5 and State Name slicer is filtered:

 

Screenshot_3.png

 

And when I check ClientKey directly from the Fact table it works as expected: 

 

Screenshot_4.png

 

Don't understand why relations do not work between fact table and dimensions when I use measure.

 

In fact, I need the measure of BKFC total, to calculate further the percentage of BKFC to BKFC Total.

To calculate percent I use the next measure:

BKFCPercent = var bigTotal = SUMX(ALL(FactTable[StateKey]), CALCULATE(SUM(FactTable[BKFC]))) return divide(sum(FactTable[BKFC]), bigTotal)

But the table is not filtered by StateKey as well.

 

Have I missed something?

 

Thanks

 

 

1 ACCEPTED SOLUTION

Hi @Long,

 

You can try to use below formula to calculate with the slicer, it not affected with the static value measure.

 

BKFCPercent = 
var bigTotal = SUMX(ALLSELECTED(FactTable),[BKFC])
return 
divide(sum(FactTable[BKFC]), bigTotal)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

6 REPLIES 6
v-shex-msft
Community Support
Community Support

Hi @Long,

 

I build a simple sample to test, it works well on my side, can you share the reproduce sample to test?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Sure, here is the test report with two pages, one with the measure column and one without: Test Report

Hi @Long,

 

Can you please provide the pbix file to test?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

HI @v-shex-msft

 

You can download it here: TestReport.pbix

Hi @Long,

 

You can try to use below formula to calculate with the slicer, it not affected with the static value measure.

 

BKFCPercent = 
var bigTotal = SUMX(ALLSELECTED(FactTable),[BKFC])
return 
divide(sum(FactTable[BKFC]), bigTotal)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Sure, here is the test report with two pages, one with the measure column and one without:

https://app.powerbi.com/view?r=eyJrIjoiNzlmNzMzODQtNDM0ZS00YjA0LWJhNmYtMWNkMGQwZTNlYjRlIiwidCI6ImZlY...

 

It looks that both dimensions do not react on the table with measure column!

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.