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.
Hi experts,
I have the following Dax code to get "Number of ranked invoices" in certain ranges.
Now I need one more information, that I need to filter my table "Invoice" in the report. The Classification.
I dont know how to get the Classification in a table column now. I sure can see it in DAX STudio as a temporary column. But I dont know how to get it as a column that I can filter for in my report.
I'm a newbie in DAX and POwer BI. Sorry.
From the logic, I would like to make a lookupvalue() with my "Invoiceid" in the temporary table "VAR _InvoicesInClass" to get the class for each row in "Inovice" at the end. But that doesnt work somehow.
Thanks for your help!!
Table1 Invoice
Invoiceid | Price | Customer | account number | Number of ranked invoices (code below) | CLASS (from Invoice Classification; want to add that)
Table2 Invoice_Classification
CLass Start End Order Class Name
Thats the DAX code for measure "Number of ranked invoices":
MEASURE Invoice[Number of Ranked Invoices] =
VAR _SelectedInvoices =
ALLSELECTED( Invoice )
VAR _NumberOfSelectedInvoices =
VAR _DistinctInvoiceId =
CALCULATE( DISTINCTCOUNT( Invoice[InvoiceId] ), _SelectedInvoices )
RETURN
IF( _DistinctInvoiceId >= 10, _DistinctInvoiceId, 10 )
VAR _RankedInvoices =
ADDCOLUMNS(
_SelectedInvoices,
"@Rank", RANKX( _SelectedInvoices, [Invoices Price],, DESC, SKIP )
)
VAR _ClassificationWithRanks =
ADDCOLUMNS(Invoice_Classification
,
"@MinimumRank", Invoice_Classification[Start] * _NumberOfSelectedInvoices,
"@MaximumRank", Invoice_Classification[End]* _NumberOfSelectedInvoices
)
VAR _InvoicesInClass =
FILTER(
CROSSJOIN( _RankedInvoices, _ClassificationWithRanks ),
AND( [@Rank] > [@MinimumRank], [@Rank] <= [@MaximumRank] )
)
VAR _Result =
CALCULATE( DISTINCTCOUNT( Invoice[InvoiceId] ), KEEPFILTERS( _InvoicesInClass ) )
RETURN
_Result
Solved! Go to Solution.
Hi @IcingOnTheCake ,
I updated my sample file and created two measures to replace the previous calculated columns since you want to display data dynamically.
Best Regards
Thats what I wanna do as a screenshot. I need the dax formula for the class column ( I've hardcoded the classification values as an example)
Thank you
Thank you @v-yiruan-msft
I've created a pbix file as an example, but I honestly dont know how to upload to this post. I only find image and video upload options. So sorry.
How can I upload that file?
@IcingOnTheCake , Are trying to filter Rank, based on slicer values ??
refer if this example can help
For Rank Refer these links
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...
Thank you for your fast answer!
But thats not exactly what I'm looking for.
I dont only need the Top N I need ranges.
Maybe its clearer when I post the classification table.
So I would like to show the invoice table and the classification in a column of this table.
based on whats currently filtered.
Class Start End Order Class Name
Top | 0 | 0,1 | 1 | Top (10 %) |
High | 0,1 | 0,3 | 2 | High (20%) |
Medium | 0,3 | 0,6 | 3 | Medium (30%) |
Low | 0,6 | 1 | 4 | Low (40%) |
Hi @IcingOnTheCake ,
Could you please provide the original data in your model tables and expected result with calculation logic and examples? Thank you.
Best Regards
Thank you @yingyinr
I've created a pbix file as an example, but I honestly dont know how to upload it to this post. I only find image and video upload options. So sorry.
How can I upload that file?
Thats what I wanna do as a screenshot. I need the dax formula for the class column ( I've hardcoded the classification values as an example)
Thank you
Hi @IcingOnTheCake ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
In addition, you can refer the content in the following video to achieve it.
Grouping & Segmenting Your Data With DAX Measure Logic - Advanced DAX
If the above ones is not working for your scenario, please provide some sample data with Text format and your expected result with calculation logic and examples. Thank you.
Best Regards
Thank you.
In my example the "RANK" formula doesnt work correct. So the class aalso not.
I assume its because there are many more companies in the table than just in the example only "BE". As well there are several slicers that could be selected. (but it also doesn't work with all slicers set to "all".
So my question:
instead of
Hi @IcingOnTheCake ,
I updated my sample file and created two measures to replace the previous calculated columns since you want to display data dynamically.
Best Regards
Thank you @v-yiruan-msft that you try to help me so much! I really appreciate.
I've added your solution. But now I get that error, as soon as I add the measure "class measure"
to my table visual where the classification should be shown.
Any idea why?
Hi @IcingOnTheCake ,
Please review the content in the following links and check whether they can help you resolve the problem.
Unexpected error occurred (file 'tmsavepointvalidation.cpp', line 458
Version: 2.79.5768.721 64-bit (mars 2020) - Error when "Apply query changes"
KB4014013 - FIX: Unexpected error occurs when you process a table in a tabular model in SSAS 2016
Best Regards
Hi again,
ok. That unexpected error disappeared after reopening the file.
At the end the below is the solution.
I had to add one more line to your code.
Had to do this, because all filters where always (company, slicers....) ignored and only with that line:
If (isempty(invoice),blank(),..........)
it worked.
Actually I dont know why this is needed. I found it while googling but without any useful explanation.
Can you explain, why this is needed?
Thank you and thank y for all your help with the code!
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 |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |