How to get dynamic Values in an x-Axis

[AlPacino]

Hey everybody,

I have a Visualisation problem which I wasn't able to solve for the last few days and now I hope somebody can help me.

My data looks like this:

Table 1:

ID	Name
A	Salesman A
B	Salesman B

Table 2:

Table1.ID	Order ID	Date
A	2234	2024/01/31
A	62	2022/02/28
B	441	2023/05/31
A	312	2023/01/02

Table 3:

Date

01...

02...

03...

04...

Now I want to build groups with the orders per salesman. I did this by creating a table SUMMARIZE(Table 1, Table 1[ID], "Groups", IF(DISTINCTCOUNT(Table 2[Order ID]) < 5...), so i had my groups as a calculated column and was able to use them as X-axis in an visual, which worked perfectly until I realized, its to aggregated to filter for the date.

Is there any possibility to transform my data to a point, where i have all IDs from Table 1 with all Orders from table 2 and the Date in a way i can build "dynamic groups" which react to slicers on the Date and the Table1.ID?

Thank you!

[Anonymous]

Hi  @AlPacino ,

It seems you're looking to create dynamic groups in your visual that can react to slicers based on Date and Table1.ID. 

• Ensure that there are relationships between Table 1 and Table 2 using the ID column, and between Table 2 and Table 3 using the Date column. 
• Then create a measure.

Dynamic Group = 
VAR OrderCount = CALCULATE(DISTINCTCOUNT('Table 2'[Order ID]), ALLSELECTED('Table 3'[Date]))
RETURN
IF(OrderCount < 5, "Group 1", "Group 2")   

• In your visual, use the measure on the X-Axis

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlPacino]

Hi @Anonymous ,

thank you very much for your quick answer. The measure does exactly what i want, but I cant use it as X-Axis in my Visual. Is there any workaround? 

best regards 

AlPacino 

[Daniel29195]

@AlPacino 

you need the grouping to change base on your slicers ? 

[AlPacino]

Exactly. I want the Groups on the x -Anis and a he number of salesmen as value. And it should react on the slicers. Do you have any idea for it?