Forum Discussion
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:
| Date | Bird ID | Count | Month | Region |
| 1/01/2019 | B01 | 1 | 1 | A |
| 2/01/2019 | B02 | 2 | 1 | A |
| 3/01/2019 | B03 | 3 | 1 | A |
| 4/01/2019 | B01 | 4 | 1 | A |
| 5/01/2019 | B05 | 5 | 1 | A |
| 1/02/2019 | B02 | 6 | 2 | A |
| 2/02/2019 | B02 | 7 | 2 | A |
| 3/02/2019 | B05 | 8 | 2 | A |
| 4/02/2019 | B06 | 9 | 2 | A |
| 5/02/2019 | B07 | 10 | 2 | A |
| 1/03/2019 | B07 | 11 | 3 | A |
| 2/03/2019 | B02 | 12 | 3 | A |
| 3/03/2019 | B03 | 13 | 3 | A |
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:
| Month | Bird ID | Count | Sighting | Region |
| 2 | B01 | 0 | No | A |
| 2 | B02 | 13 | Seen | A |
| 2 | B03 | 0 | No | A |
| 2 | B05 | 8 | Seen | A |
| 2 | B06 | 9 | New | A |
| 2 | B07 | 10 | New | A |
| 3 | B01 | 0 | No | A |
| 3 | B02 | 12 | Seen | A |
| 3 | B03 | 13 | Seen | A |
| 3 | B05 | 0 | No | A |
| 3 | B06 | 0 | No | A |
| 3 | B07 | 11 | Seen | A |
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.
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")))
5 Replies
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:
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)PBIX attached.
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.
