Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi guys and girls,
I have the following situation, simplified from my original challange. I want to count a grouping measure to create a segmentation of customers depending on their performance (good, bad, ect.)
Here the data and data model
I created a range table manually
Range table
and a support table with DAX
supporttable = SUMMARIZECOLUMNS(SalesTable[Customer], "code", [Margin check code])
Support table
Solved! Go to Solution.
Hello @datadonuts ,
SUMMARIZECOLUMNS functions has some limitations described in this article: summarizecolumns (see Remarks sections).
An alternative is to use SUMMARIZE + ADDCOLUMNS.
Here is your measure:
margin check total group VIRTUAL =
var vtab2 = ADDCOLUMNS(SUMMARIZE(SalesTable, SalesTable[Customer]), "code",[Margin check code])
VAR selectedcode = SELECTEDVALUE(MarginRange[Code])
VAR returnsum =
CALCULATE(
COUNTX(vtab2,[code]),
FILTER(vtab2,[code] = selectedcode)
)
RETURN
returnsum
And by the way, it shows correct value in totals in this case.
Did I answer your question? Mark my post as a solution!
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Hello @datadonuts ,
SUMMARIZECOLUMNS functions has some limitations described in this article: summarizecolumns (see Remarks sections).
An alternative is to use SUMMARIZE + ADDCOLUMNS.
Here is your measure:
margin check total group VIRTUAL =
var vtab2 = ADDCOLUMNS(SUMMARIZE(SalesTable, SalesTable[Customer]), "code",[Margin check code])
VAR selectedcode = SELECTEDVALUE(MarginRange[Code])
VAR returnsum =
CALCULATE(
COUNTX(vtab2,[code]),
FILTER(vtab2,[code] = selectedcode)
)
RETURN
returnsum
And by the way, it shows correct value in totals in this case.
Did I answer your question? Mark my post as a solution!
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Thanks so much, great solution. I hope one day I can figure it out by myself in just minutes like you did, bc it took me a long, long, long time to get to my solution (I won't tell you how long 😉
May I kindly follow up the solution with some more question to better understand the mechanics behind that
1. Why it does not need the TREATAS function? As there is no relationship between the range table and the support/virtual table, I was thinking I need to build one. Why is that not necessary amd still works?
2. You also removed the VALUES in the FILTER condition. I always believed, that you need VALUES in order to create a single column for the filter to iterate through, otherwise you get the error "Multiple columns ...."
Finally please tell me the secret which part of your solution generates the correct total. I tweaked my solution a bit to recreate it, but only your solution shows the correct total. Seems to be another hidden miracle in DAX I still don't understand.
Thanks again, really appreciate your help.
Hi, sorry for the late response.
I believe it wasn't the fanciest solution but it works (I'm also in the process of learning 🙂).
P.S. For the virtual table another code may be used with the same result in this particular case:
var vtab2 = ADDCOLUMNS(DISTINCT(SalesTable[Customer]), "code",[Margin check code])
1. selectedcode variable gets code for each description from the MarginRange table. So for each row you just count the codes that are equal to the current one.
2. If you try to use virtual table with the VALUES funtion, you'll get an error: The VALUES function expects a column reference expression or a table reference expression for argument '1'.
You can either filter the whole table (not the best approach) or a column/number of columns. I'm not sure how to restrict a virtual table to a single column in this case.
3. There is a good article on measure totals (short answer - it depends on the context):
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/m-p/63376/highlig...
For your first measure you could use HASONEFILTER or HASONEVALUE function to get the correct result in total.
DAX Studio can show you what it was calculating "behind the scenes":
SET DC_KIND="AUTO";
SELECT
COUNT ( )
FROM 'Table'
WHERE
'Table'[code] IN ( null, 0 ) VAND
'Table'[code] IS NOT NULL;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
User | Count |
---|---|
21 | |
20 | |
15 | |
10 | |
7 |
User | Count |
---|---|
29 | |
28 | |
12 | |
12 | |
12 |