Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I’m trying to create something like:
Countrows ( distinct ( selectcolumns ( allexcept ( ‘table 1’ , ‘table 1’[column A] , ‘table 2’[column A] ) , “test” , ‘table 1’[id] ) ) )
But allexcept doesn’t allow me to use a column from a second table in a measure. How do I make this work? The measure is intended for a matrix containing both internal and external filters. Only a part of the internal and all of external filters should remain.
Hi @DouweMeer
You might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive or dropbox and share the link to the file. Please do not forget to describe the expected results based on this sample data.
Due to my companies policy, I'm unable to share any data. If you know a trick where I can upload/ create an excel and share it here, let me know. But things like Box, OneDrive, DropBox and similar are block by IT policy.
I was hoping that the DAX expression would suggest enough. The problem is that in a measure allselected and allexcept are filter functions that only work in its own table. So if you want filters removed/ maintained from a secondary table, you can't. I'm looking for a way past it. For now my only idea is to use allselected on the required table and reapply the internal filters. How I will apply filters from a second table on a DAX table reference, I don't know yet.
Hi @DouweMeer
You can copy the simple table and paste it here. list the table1,table2,and the expected results. we'd like to provide further help.
Let me see....
Table1 | ||||
Case | Product | Time | Source | Type |
1 | A | 3 | External | Complaint |
2 | B | 5.5 | External | Complaint |
3 | A | 2.7 | External | Complaint |
4 | A | 1.5 | Internal | Analytics |
5 | B | 6 | External | Complaint |
Quite fancy
Table2 | ||
Product | Type | Prototype |
A | Electric | Yes |
B | Gas | No |
C | Electric | No |
D | Gas | Yes |
Relationship between table 1 and 2 is via the Product field.
So I get a matrix as follows:
Yes | No | ||||
Electric | Gas | Electric | Gas | ||
External | Complaint | ||||
Analytics | |||||
Internal | Complaint | ||||
Analytics |
And for each square in the matrix I need 2 calculations.
Calculation 1 : takes a distinct count of cases, the context is all cases but with the filters External and Internal maintained plus all external filters (Teams, time period, etc)
Calculation 2 : the other one takes a distinct count of products from table 1 or 2 (by relationship it shouldn't matter). The distinct count product is in the context it is put into by the matrix itself.
(eventually I'm curious to the ratio)
Hi @DouweMeer
Sorry I still don't quite understand your requirement, based on your info, would you like to get below results?
Pbix attched.
Hello @v-diye-msft
Thank you for your attempt :). Comes close.
Yes | No | Total | ||||||||||
Electric | Gas | Electric | Gas | |||||||||
Calc1 | Calc2 | Calc1 | Calc2 | Calc1 | Calc2 | Calc1 | Calc2 | Calc1 | Calc2 | |||
External | Complaint | 4 | 1 | 4 | 4 | 4 | 1 | 4 | 2 | |||
Analytics | 0 | 0 | ||||||||||
Internal | Complaint | 0 | 0 | |||||||||
Analytics | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
This would be my table. Calc1 shows the same values for each column as the context from the columns should be excluded. Calc2 does include them. So I would consider Calc2 not to be the difficult one, but Calc1 is. Allselected doesn't work as the filters from the columns come from the product table rather than the case table, but distinctcount ( 'Table1'[Case] ).
As far as I understand is that you can't manipulate the context by DAX of a relationable table while having another as the base for your calculations.
Hi @DouweMeer
What about this one?
Pbix attached.
This is more a 'cheat way' that works in this example. You're removing all filters from everywhere. However, in my situation, there are also explicit filters in the second table as well as a third table. With Allexcept you would also remove those filters.
Getting back to my original one:
D ( A ( ( C ) , B ) )
Part A
The table reference
selectcolumns ( ... )
Part B
The field calculated (distinctcount cases)
... ) , “test” , ‘table 1’[id] )
Part C
The Context
(...) allexcept ( ‘table 1’ , ‘table 1’[column A] , ‘table 2’[column A] )
Note C1: 'Table1'[Column A] would refer to the implicit filter (External/ Internal).
Note C2: 'Table2'[Column A] would refer to an explicit filter in the second table. This could also be a third table.
Part D
Final calculation
Countrows ( distinct ( ... ) )
So far I'm struggling at the context (Part C) as Allexcept (...) won't allow to have a column from a second table to be included in the allexcept funciton. My second thought was to remove implicit filter and reapply them by a combination of selectedvalue and allselect. However, if one of the selectedvalues is from a second table, I'm not aware of a DAX function that can apply a value in a second related table.
Perhaps a combination of allselected, selectedvalue and userelationship?
@DouweMeer it is not clear what you are trying to do. Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |