Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Team, Can someone please help me with the below request?
I have a SalesTransaction table like below. I need to create a Flag column with this condition.
Condition: First I have to find the Transaction IDs for Account ID = 3 and Flag it as TRUE and Also, I have to check if those transactions IDs (in Account ID =3) are repeating for any other Account IDs. If yes, I have to Flag those rows also as TRUE.
I'm able to find those records in SQL with the below code
SELECT * FROM SalesTransaction
WHERE TRANSACTION ID IN (select TRANSACTION ID from [SalesTransaction] WHERE
[ACCOUNT ID] = 3)
Account ID | Transaction ID | Transaction Amount | Flag |
1 | 234 | 387 | TRUE |
1 | 345 | 3456 | TRUE |
1 | 456 | 5644 | FALSE |
2 | 987 | 345 | TRUE |
2 | 876 | 5677 | FALSE |
3 | 234 | 68956 | TRUE |
3 | 345 | 4567 | TRUE |
3 | 987 | 567 | TRUE |
4 | 123 | 457 | FALSE |
4 | 234 | 5678 | TRUE |
Not sure how to do this in Power BI. Can someone please help me with this.
Thanks!!
Solved! Go to Solution.
Hi, @Anonymous
You can try to create a calculated column as below and apply it as a visual filter.
New Flag =
VAR _tab1 =
SUMMARIZE (
FILTER ( ALLSELECTED ( SalesTransaction ), SalesTransaction[ACCOUNT ID] = 3 ),
SalesTransaction[TRANSACTION ID]
)
RETURN
IF (
COUNTROWS (
FILTER (
_tab1,
SalesTransaction[Transaction ID] = EARLIER ( SalesTransaction[Transaction ID] )
)
) > 0,
TRUE (),
FALSE ()
)
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , You can try a measure like
measure =
var _tab = summarize(filter(allselected(SalesTransaction),Table[ACCOUNT ID] = 3), Table[TRANSACTION ID])
return
countrows(filter(SalesTransaction,Table[SalesTransaction] in _tab))
or use this as a visual level filter and check for not blank
Hey @amitchandak Thanks for your quick response. Can you please check the last line of your code please. I think I'm missing something. I didn't get Table[SalesTransaction] and more over I want to create it as a new Flag column.
Thanks!!
@Anonymous , Sorry, My mistake.
Please check now
measure =
var _tab = summarize(filter(allselected(SalesTransaction),Table[ACCOUNT ID] = 3), Table[TRANSACTION ID])
return
countrows(filter(SalesTransaction,SalesTransaction[TRANSACTION ID] in _tab))
@amitchandak - Looks like your measure is working. But, since it is a measure I'm not able to use it as Slicer.
Also, If I use this measure as a visual filter, I notice a huge performance issue. The table taking very long time to load everytime.
Thanks!
Hi, @Anonymous
You can try to create a calculated column as below and apply it as a visual filter.
New Flag =
VAR _tab1 =
SUMMARIZE (
FILTER ( ALLSELECTED ( SalesTransaction ), SalesTransaction[ACCOUNT ID] = 3 ),
SalesTransaction[TRANSACTION ID]
)
RETURN
IF (
COUNTROWS (
FILTER (
_tab1,
SalesTransaction[Transaction ID] = EARLIER ( SalesTransaction[Transaction ID] )
)
) > 0,
TRUE (),
FALSE ()
)
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
90 | |
82 | |
76 | |
64 |
User | Count |
---|---|
144 | |
109 | |
108 | |
100 | |
96 |