cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anandav
Continued Contributor
Continued Contributor

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.
anandav
Continued Contributor
Continued Contributor

@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?

anandav
Continued Contributor
Continued Contributor

@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

anandav
Continued Contributor
Continued Contributor

@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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors