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
Hi,
I really need assistance in solving a problem in power bi.
I want to create a calculated column using if statement. I have a table with customer ids and a monthly report from 2019 to present.
I want to identify which unique customer ids have transactions in each monthly report.
Report Date | Loyalty Card Number | Liters | Result |
Nov-19 | AC528974 | 20 | |
Apr-19 | AC528974 | 9 | |
May-19 | AC528974 | 33 | |
Dec-19 | AA062113 | 10 | |
May-19 | AA679780 | 9 | |
Nov-19 | AB333164 | 10 | TRUE |
Sep-19 | AB333164 | 2 | TRUE |
May-19 | AB333164 | 2 | TRUE |
Jun-20 | AB333164 | 2 | TRUE |
Jun-20 | AB333164 | 2 | TRUE |
Dec-19 | AB333164 | 3 | TRUE |
Apr-19 | AC813202 | 9 | |
Mar-20 | AB570381 | 5 | TRUE |
Mar-20 | AB570381 | 3 | TRUE |
May-20 | AB333164 | 22 | |
Jul-20 | AB333164 | 20 | |
Jul-20 | AC627150 | 10 | |
Aug-20 | AB333164 | 3 | |
Aug-20 | AB333164 | 4 | TRUE |
Aug-20 | AB333164 | 8 | TRUE |
Aug-20 | AB333164 | 5 | TRUE |
Sep-20 | AB333164 | 5 | TRUE |
Sep-20 | AB333164 | 7 | TRUE |
Jul-20 | AB333164 | 7 | TRUE |
Jul-20 | AB570381 | 27 | TRUE |
Aug-20 | AB570381 | 14 | TRUE |
I want to add the value "True" for the customer ids that will pass the condition same with result on the table above.
Please confirm if this is possible or there are other ways to create this result, perhaps in power query?
Really hoping someone can help me.
Thanks in advance.
CountRows(Filter(all(MonthlyReport[ID],MonthlyReport[Date]),Format(MonthlyReport[Date],"yyyymm")=Format(Customer[Date],"yyyymm")&&MonthlyReport[ID]=Customer[ID]))>0
Hi@AREO-07
Do you mean they have to have data in all the dates present in the table. If so, none of the IDs shown complies.
1. Place Loyalty Card in a table visual
2. Create this measure and place it in the visual
Measure =
VAR allDates_ =
ALL ( Table1[Report Date] )
VAR currentIDDates_ =
DISTINCT ( Table1[Report Date] )
VAR missingDates_ =
COUNTROWS ( EXCEPT ( allDates_, currentIDDates_ ) )
RETURN
IF ( missingDates_ = 0, TRUE (), FALSE () )
It would be better to have an additional table with the dates so that you can use that as reference for the period in which you want the cards to be active
This can also be implemented in Power Query
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
I have created a dax measure but i am having issues with the calculate function what could be the proper workaround here
Hoping for your response.
Thanks
Maybe my logic is incorrect.
But what i need is to identify which card numbers have transactions every monthly report and group them so that i could create a graph that shows how frequent they are transacting each month.
See my table below.
@AREO-07
Not clear enough, could you explain more on how the logic should be applied to mark as TRUE
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I want to mark "True" if that unique card number shows in every monthly report.
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 |
---|---|
31 | |
16 | |
14 | |
14 | |
9 |