The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi All,
I am trying to retrieve / filter rows in a report based on a where clause.
The scenario is that each quote (Id) will have an Estimate and when work has been done, will have a Final.
Each Id can additionally have multiple types based on the Rate (Work carried out during Day rates or Night Rates)
I want to be able to filter for a report and retrieve the rows where the Type is Final if the Id has a Final, or retrieve Estimate where it doesn't.
My imported data looks like this:
What I want to display in my report:
Solved! Go to Solution.
Hi @Anonymous ,
I create a sample to have a test. My sample is the same like yours.
Measure:
Filter Measure =
VAR _Type_List =
CALCULATETABLE (
VALUES ( 'Table'[Type] ),
ALLEXCEPT ( 'Table', 'Table'[QuoteId] )
)
RETURN
IF (
"Final" IN _Type_List,
IF ( MAX ( 'Table'[Type] ) = "Final", 1 ),
IF ( MAX ( 'Table'[Type] ) = "Estimated", 1 )
)
Create a table visual , add this measure into visual level filter and set it to show items when value =1.
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Use this measure as visual level filter
countx(filter(summarize(Table, Table[QuoteID], Table[Type], "_1", countx(filter(allselected(Table), Table[ID], max(Table[ID]) && Type = "Final")),, "_2", countx(filter(allselected(Table), Table[ID], max(Table[ID]) && Type = "Estimated"))), (not(Isblank(_1)) && [Type] ="Final") || ((Isblank(_1)) && [Type] ="Estimated")), [QuoteID])
check for not blank. Else create all measure using this code
Unfortunately I am receiving this error when using that measure as a filter:
Hi @Anonymous ,
I create a sample to have a test. My sample is the same like yours.
Measure:
Filter Measure =
VAR _Type_List =
CALCULATETABLE (
VALUES ( 'Table'[Type] ),
ALLEXCEPT ( 'Table', 'Table'[QuoteId] )
)
RETURN
IF (
"Final" IN _Type_List,
IF ( MAX ( 'Table'[Type] ) = "Final", 1 ),
IF ( MAX ( 'Table'[Type] ) = "Estimated", 1 )
)
Create a table visual , add this measure into visual level filter and set it to show items when value =1.
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
80 | |
78 | |
37 | |
34 | |
31 |
User | Count |
---|---|
93 | |
81 | |
60 | |
49 | |
49 |