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

Create 2 filters from one field and use them to populate a table

Hi All,

 

I have a table that looks like this:

 

MonthStatusNumber of people
Apr-21Prospects80
Apr-21Warm Leads15
Apr-21Hot Leads10
May-21Prospects70
May-21Warm Leads15
May-21Hot Leads20
Jun-21Prospects100
Jun-21Warm Leads30
Jun-21Hot Leads8

 

I want to create 2 filters from the date field and populate a table below:

 

Anantha1_0-1632762410345.png

Apr-21 and Jun-21 should appear as dropdowns . Could anyone please help me with this?

 

@PaulDBrown  @amitchandak  @Ashish_Mathur @Greg_Deckler  @parry2k  @VahidDM @moizsherwani  @Fowmy  

1 ACCEPTED SOLUTION
Shishir22
Solution Sage
Solution Sage

Hello @Anonymous -

 

This might be a workaround.

 

1. Create 2 DimCalendar stand alone(without relationship to data table) date dimension table.

 

Table 1-   MinCalendar =Calendarauto()

 

Shishir22_0-1632764600406.png

 

Table 2 - MaxCalendar= Calendarauto()

 

Shishir22_1-1632764684380.png

 

In both of these table create calculated column as-

MinSelection and MaxSelection respectively.

 

Shishir22_2-1632764736583.png

 

Please note there will be not any relationship between these three tables.

 

Shishir22_3-1632764783061.png

 

Use these MinSelection and MaxSelection columns as slicer dropdown.

 

Create calculated column in your datatable as:

 

Shishir22_4-1632764871022.png

 

Create 3 measures as:

 

MinValue = 
Var _minSelected = SELECTEDVALUE(MinCalendar[Min Selection])
VAR _Result = CALCULATE(SUM(Data[Number of people]),FILTER(Data,Data[Column]=_minSelected))
Return
_Result

 

MaxValue = 
Var _maxSelected = SELECTEDVALUE(MaxCalendar[Max Selection])
VAR _Result = CALCULATE(SUM(Data[Number of people]),FILTER(Data,Data[Column]=_maxSelected))
Return
_Result

 

%change = DIVIDE([MaxValue]-[MinValue],[MinValue],BLANK())

 

Change format of %change measure to percentage.

 

Shishir22_5-1632764971311.png

Please mark this post as solution if it fulfils your requirement. Kudos are also accepted.

Cheers!

Cheers,
Shishir

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

@Shishir22  thank you so much, this is very useful!

 

@parry2k  Yes, this is what I was looking for

Shishir22
Solution Sage
Solution Sage

Hello @Anonymous -

 

This might be a workaround.

 

1. Create 2 DimCalendar stand alone(without relationship to data table) date dimension table.

 

Table 1-   MinCalendar =Calendarauto()

 

Shishir22_0-1632764600406.png

 

Table 2 - MaxCalendar= Calendarauto()

 

Shishir22_1-1632764684380.png

 

In both of these table create calculated column as-

MinSelection and MaxSelection respectively.

 

Shishir22_2-1632764736583.png

 

Please note there will be not any relationship between these three tables.

 

Shishir22_3-1632764783061.png

 

Use these MinSelection and MaxSelection columns as slicer dropdown.

 

Create calculated column in your datatable as:

 

Shishir22_4-1632764871022.png

 

Create 3 measures as:

 

MinValue = 
Var _minSelected = SELECTEDVALUE(MinCalendar[Min Selection])
VAR _Result = CALCULATE(SUM(Data[Number of people]),FILTER(Data,Data[Column]=_minSelected))
Return
_Result

 

MaxValue = 
Var _maxSelected = SELECTEDVALUE(MaxCalendar[Max Selection])
VAR _Result = CALCULATE(SUM(Data[Number of people]),FILTER(Data,Data[Column]=_maxSelected))
Return
_Result

 

%change = DIVIDE([MaxValue]-[MinValue],[MinValue],BLANK())

 

Change format of %change measure to percentage.

 

Shishir22_5-1632764971311.png

Please mark this post as solution if it fulfils your requirement. Kudos are also accepted.

Cheers!

Cheers,
Shishir
parry2k
Super User
Super User

@Anonymous is this what you are looking for?

 

parry2k_0-1632763590369.png

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.