Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
My requriment is this, I have two tables, one contains mobile phone numbers and other has usage details for those numbers.
In the report there is a date slicer wich we can select number of months that we want to see inactive mobile numbers.
The trick is, if a mobile number is active in Januery and inactive in other months, as the current month is Augest if we select 8 months the perticuler number should not come to the report as it is active in that selected period. But if we select 6 months from the slicer that number should apper as inactive witthin that period.
What I thought is to create two tables for both active and inactive numbers based on the dates. And in the report, filter the active numbers out based on the slicer date range.
What are the ways to achive this in Power BI ?
Below are the samples of table details as I see some of you got mis understood the senario.
Active_phonenumbers
Telephone | Invoice_date | Usage_ID |
12345 | 10/Jan/2020 | 1 |
54321 | 10/Feb/2020 | 1 |
54321 | 10/Mar/2020 | 2 |
54321 | 10/Jan/2020 | 1 |
Inctive_phonenumbers
Telephone | Invoice_date | Usage_ID |
54321 | 10/Jan/2020 | 2 |
12345 | 10/Feb/2020 | 1 |
12345 | 10/Mar/2020 | 1 |
12345 | 10/Jan/2020 | 1 |
Now if I select
I should get 12345 as Inactive. That's not a problem for me.
The problem is if I select 8 months as below
I should NOT get 12345 as inactive because the number has an active record in Jan 2020. (within the slicer date range).
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi @Anonymous
I would crea a measure for this purpose
Assuming as no detail about tabel structure is provided. Below are the columns names and data it will store.
Mobile - has mobile number
Date - is date of active inactive
status - stores active or inactive
Active_or_inactive = CALCULATE(MIN(table[status]), allexcept(table,table[date],table[mobile]))
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
Thank for the reply @pranit828 and @AllisonKennedy . I have updated the the question for more clarification.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi @AllisonKennedy The number 12345 is active only on Jan the inactive record for Jan is 54321. Which you may confused.
I have created these two tables becase there are few other logics to identify as Active and inactive. For example, usage details, subscription to date etc...
Furthermore, I have added keys to relate these two tables as Telephone+Year(Invoice_date)+Month(Invoice_date)
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@AllisonKennedy Dim Date is already there but not dim phone numbers. I can add that.
Please note that this is just a small part of a big solution which is in production and the data model is not desined by me. Therefore, I cannot make big changes to the data model as it may break other bunch of reports.
The other question you asked I have put 12345 and also one record of 54321 to differentiate. Those two tables records are as it is I entered.
Hi @Anonymous
I would keep only one table with on more column which says 'Status' and would use the below.
Slicing the table into two for just active/inactive might not be the best of solution.
Active_or_inactive = CALCULATE(MIN(table[status]), allexcept(table,table[date],table[Telephone]))
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |