Need help to build the logic and visual

[deepakkumar456]

Hello,

I was struggling to get data for one of the scenrio for my report visual. Basically my report is modeled based on multiple dimension table and a fact table with "1 to many" relationship. I can easily get the count for any attribute from my fact table based on filter on dimension table. But here I need to get the count from dimension table based on the grouping from fact table value. To explain in detail I have created a below illustration - 

Table : DimBucket	
	
BucketId	BucketName
1	        Bucket Name 1
2	        Bucket Name 2
3	        Bucket Name 3
4	        Bucket Name 4
5	        Bucket Name 5

Table : ColorFact		
		
ColorId	Color Name	BucketId
1	     Green	      1
2	       Red	      1
3	    Yellow	      1
4	     Green	      2
5	      Blue	      2
6	    Yellow	      3
7	      Pink	      3
8	     Black	      3
9	       Red	      3
10	     White	      3

I need the data in below format so that I can draw a line graph (X-Axis : Bucket Name, Y-Axis : Any other Numeric column may be a color rating [e.g. 1,2,3,4,5 etc.], Legend : Color)

Color	BucketCount
Green	     2
Red	         2
Yellow	     3

 Thanks in advance for the help !!

[v-tsaipranay]

Hi @deepakkumar456 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[deepakkumar456]

Thank you for your detailed response.  This helps a lot with the requested query. 

Could you please provide additional guidance on my problem -

There is a page level filter (Multi-select) of BucketName on my report and I want that filter to be applied before the SUMMARIZE. Then how I need to write my ColorBucketSummary measure for table, so that it only aggregate for the current selected Bucket. 
Just to let you know that if I don't apply this filter logic in the calculation then it will aggregate the entire data and my filter doesn't work on that calcualted table data.

Thank you again for your help on this.

[v-tsaipranay]

Hi @deepakkumar456 ,

Sorry for the delay response. You're correct, when using a calculated table such as ColorBucketSummary, it is evaluated at model processing time and does not reflect dynamic filter interactions (like page-level filters

To on BucketName).ensure that your aggregation respects report-level filters such as a multi-select on BucketName, I would recommend switching from a calculated table to a DAX measure, which is evaluated at query time and always respects slicers and filters applied in the report.

You can use the following measure version of your logic:

ColorBucketCount =
CALCULATE(
    DISTINCTCOUNT(ColorFact[BucketId]),
    KEEPFILTERS(VALUES(DimBucket[BucketName]))
)

Or, if you're using visuals where Color Name is on the legend or axis, and you want this count per color:

ColorBucketCount =
CALCULATE(
    DISTINCTCOUNT(ColorFact[BucketId]),
    KEEPFILTERS(VALUES(DimBucket[BucketName])),
    ALLEXCEPT(ColorFact, ColorFact[Color Name])
)

KEEPFILTERS ensures that any filters applied from slicers or page-level filters on BucketName are preserved when evaluating the measure.

Then, use this measure in your visual in X-axis: Color Name in Y-axis: ColorBucketCount, for optional filter use BucketName slicer or page-level filter.

This will dynamically show the count of distinct Buckets per Color, filtered based on selected Buckets.

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

Thankyou.

[v-tsaipranay]

Hi @deepakkumar456   ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-tsaipranay]

Hi @deepakkumar456 ,

Thank you for reaching out to the Microsoft Fabric Community.

I wanted to check if you had the opportunity to review the information provided by @DataNinja777 . Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[DataNinja777]

Hi @deepakkumar456 ,

To get the count of how many unique Buckets each Color appears in, you can use a DAX measure that calculates the distinct count of BucketId from the ColorFact table, grouped by Color Name. The following DAX measure will work:

ColorBucketCount = 
CALCULATE(
    DISTINCTCOUNT(ColorFact[BucketId]),
    ALLEXCEPT(ColorFact, ColorFact[Color Name])
)

This measure will return the number of unique BucketIds for each Color Name, ignoring other filters except for the color.

If your goal is to create a visual where the X-axis is the Color, the Y-axis is the number of buckets they appear in, and you want to draw a line or bar graph, this measure can be used directly. Place ColorFact[Color Name] on the X-axis, ColorBucketCount on the Y-axis, and optionally add color as the legend if you want multiple series (e.g., for other attributes or for animation by time).

However, if you're aiming for a structure that resembles a pre-aggregated table, you can also create a calculated table like this:

ColorBucketSummary = 
SUMMARIZE(
    ColorFact,
    ColorFact[Color Name],
    "BucketCount", DISTINCTCOUNT(ColorFact[BucketId])
)

This table can be used directly in a visual, where Color Name is the axis and BucketCount is the value.

If your visual absolutely needs to have Bucket Name on the X-axis and each color plotted as a line across buckets (even if that means plotting presence rather than count), you'd need to define a measure like:

ColorPresence = 
IF(
    COUNTROWS(ColorFact) > 0, 
    1, 
    0
)

Then place DimBucket[Bucket Name] on the X-axis, ColorPresence on the Y-axis, and Color Name on the legend. This would show a line with value 1 for each bucket where the color exists.

Let me know which visual outcome you're aiming for if this needs further tuning.

Best regards,