Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Is there a way to get date slicer from date to a parameter ?

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

TelephoneInvoice_dateUsage_ID
1234510/Jan/20201
5432110/Feb/20201
5432110/Mar/20202
5432110/Jan/20201

 

Inctive_phonenumbers

TelephoneInvoice_dateUsage_ID
5432110/Jan/20202
1234510/Feb/20201
1234510/Mar/20201
1234510/Jan/20201

 

Now if I select 

 

I should get 12345 as Inactive. That's not a problem for me.

slicer.jpg

 

The problem is if I select 8 months as below 

slicer2.jpg

 

I should NOT get 12345 as inactive because the number has an active record in Jan 2020. (within the slicer date range).

8 REPLIES 8
AllisonKennedy
Super User
Super User

Assuming you have two tables;
DimPhoneNumber
FactUsageDetails
and that these are related on the column [Number]

You could create a MEASURE that counts inactive phone numbers:
Total Inactive Numbers = COUNTROWS(FILTER(DimPhoneNumber, COUNT(FactUsageDetails[Number])<1)

This should update as you increase or decrease number of months selected from the DimDate table.

Please @mention me in your reply if you want a response.

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

pranit828
Community Champion
Community Champion

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





PBI_SuperUser_Rank@1x.png


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
Anonymous
Not applicable

Thank for the reply @pranit828 and @AllisonKennedy . I have updated the the question for more clarification.

@Anonymous
Thanks for the new info. I am more confused now, lol.

Can you please clarify if the inactive and active tables you have posted are from the raw data source or if you have created them yourself?

Looking at these two tables, it looks like 12345 is in both active and inactive for Jan 2020 - is that a typo from your sample data or do we need to tell Power BI to assume that if it's in the active table then to remove it from inactive?

This problem would be much simpler if they are in 1 table, so if you have changed the raw data at all, let us know, otherwise you can create a custom MEASURE and add that as a filter on the Inactive table:

Active Numbers = 0 + COUNTROWS(Active_phonenumbers)

Add this Active Numbers measure as a filter on the table visual showing Inactive numbers, then filter for Active Numbers = 0

On a side note, what does Usage_Id relate to?

Please @mention me in your reply if you want a response.

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

Anonymous
Not applicable

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)

Thanks @Anonymous

The bottom row in your inactive table is 10/Jan/2020 for 12345?

I forgot to mention that for my solution above to work, you will need to be using a DimDate and DimNumber tables to relate these two tables, and you will need to use that that DimNumber[Number] column and DimDate[Date] in the visual and slicer. I don't recommend creating a relationship between these two tables directly because they are both Fact tables in essence.
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

Please @mention me in your reply if you want a response.

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

Anonymous
Not applicable

@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]))




PBI_SuperUser_Rank@1x.png


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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.