Lookup a column value thats in my Temporary Table variable

[IcingOnTheCake]

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

[IcingOnTheCake]

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

[IcingOnTheCake]

Thank you  @Anonymous 

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?

[amitchandak]

@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...

[IcingOnTheCake]

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%)

[Anonymous]

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

[IcingOnTheCake]

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

[Anonymous]

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

[IcingOnTheCake]

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 

FILTER(ALL('invoice'),'invoice'[Company]=EARLIER('invoice'[Company]))

how can I say: "what is currently selected for this visual. with all slicers and filters"
Thank you

[Anonymous · Accepted 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

[IcingOnTheCake]

Thank you  @Anonymous 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?

[Anonymous]

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

[IcingOnTheCake]

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!