cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anandav
Skilled Sharer
Skilled Sharer

Creating table in dax based on user selected variable

Hi Experts,

 

I am trying to help a friend who is collected data on birds sighted in a region.

Here is the sample data:

DateBird IDCountMonthRegion
1/01/2019B0111A
2/01/2019B0221A
3/01/2019B0331A
4/01/2019B0141A
5/01/2019B0551A
1/02/2019B0262A
2/02/2019B0272A
3/02/2019B0582A
4/02/2019B0692A
5/02/2019B07102A
1/03/2019B07113A
2/03/2019B02123A
3/03/2019B03133A

 

Requirement:

a] User selects a month as the control group set (CG). This could be one or more months. e.g. CG = 1

b] The user then selects the analysis group set (AG). This could be one or more months. e.g. AG = 2 and 3

c] The output should be comparison of birds seen in CG that are either present, not present or new in AG.

Sample output:

MonthBird IDCountSightingRegion
2B010NoA
2B0213SeenA
2B030NoA
2B058SeenA
2B069NewA
2B0710NewA
3B010NoA
3B0212SeenA
3B0313SeenA
3B050NoA
3B060NoA
3B0711SeenA

 

I can get the total counts using a measure - using VARs to create set of CG and set of AG and using EXCEPT and INTERSECT.

e.g. x number of 'Seen' birds in AG

 

But the requirement is to return a table so that it can be further visualised. Since I can't have user selected variables (ALLSELECTED values of a slicer) when creating a table in DAX, I am stuck!

 

Any suggestion how I can do this in DAX or Power Query?

 

Thanks in advance.

1 ACCEPTED SOLUTION

Hi @anandav 

 

It seems I have fixed it!!!

please kindly use this measure based on my above post:

Measure = var a = MAXX(FILTER(ALL('Table 2'),[Month]<MAX('Table 2'[Month])&&[Bird ID]=MAX('Table 2'[Bird ID])),[NewCount])
Return
IF([NewCount]=0,"No",IF(a=0,"New",IF(a>0,"Seen")))

4.PNG

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

5 REPLIES 5
v-diye-msft
Community Support
Community Support

Hi @anandav 

 

I don't think it's able to create the calculated table based on your 2 slicers: CG&AG, but I can generate the new table as your requested using another method and add the measure count :

1. Generate 2 tables then use crossjoin:

Bird ID = SUMMARIZECOLUMNS('Table'[Bird ID])
Month = SUMMARIZECOLUMNS('Table'[Month])
Table 2 = CROSSJOIN('Month','Bird ID')

Untick the Month1:

6.PNG

2. Add the measure:

NewCount = var a = CALCULATE(SUM('Table'[Count]),FILTER('Table',[Month]=MAX('Table 2'[Month])&&[Bird ID]=MAX('Table 2'[Bird ID])))
Return
IF(ISBLANK(a),0,a)

7.PNG

 

PBIX attached.

 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

@v-diye-msft,

Thank you for the pbix file. It was helpful but still my requirement is to identify the birds as seen, not seen or new in the analysis group compared to the control group.

 

I am wondering whether any way to do that?

@Greg_Deckler , @Phil_Seamark , @Zubair_Muhammad , @Ashish_Mathur , @ImkeF 

Hi Experts,

As I have got good solutions from you, I am tagging you in hope of you can do the same for this problem.

Thanks in advance.

Hi @anandav 

 

It seems I have fixed it!!!

please kindly use this measure based on my above post:

Measure = var a = MAXX(FILTER(ALL('Table 2'),[Month]<MAX('Table 2'[Month])&&[Bird ID]=MAX('Table 2'[Bird ID])),[NewCount])
Return
IF([NewCount]=0,"No",IF(a=0,"New",IF(a>0,"Seen")))

4.PNG

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

@v-diye-msft,

Thanks al ot. That is a good solution! 

I really appreciate you taking the time to solve this for me.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors