Hi @Hashmeet ,
To solve the issue of using your comparison measures in visuals like cards and bar charts, we need to adjust your DAX formulas so that they can aggregate meaningful results across the entire dataset. The current setup works well in a table visual because it relies on row context to evaluate the differences between Raw_Data 1 and Raw_Data 2. However, when you want to use these measures in summary visuals, you need to aggregate the comparison outcomes meaningfully without losing granularity.
The key is to convert your comparison results into numeric values that can be counted or aggregated, rather than returning text outputs like "Dates Match" or "Qty Match." By doing this, you can create summary measures that count the number of matches, mismatches, or changes across the entire dataset.
First, modify your Date Comparison and Qty Comparison measures to return numeric values. For Date Comparison, the measure can be rewritten as follows:
Date Comparison Numeric =
VAR d1 = [Need By Date First]
VAR d2 = [Need By Date Second]
RETURN
SWITCH(
TRUE(),
ISBLANK(d1) && ISBLANK(d2), 0, // No Date Present
NOT ISBLANK(d1) && ISBLANK(d2), 1, // New Date Added
ISBLANK(d1) && NOT ISBLANK(d2), 2, // Date Removed
d1 = d2, 3, // Dates Match
4 // Dates Don't Match
)
Similarly, modify the Qty Comparison measure to return numeric values:
Qty Comparison Numeric =
VAR q1 = [Open Qty First]
VAR q2 = [Open Qty Second]
RETURN
SWITCH(
TRUE(),
ISBLANK(q1) && ISBLANK(q2), 0, // No Qty Present
NOT ISBLANK(q1) && ISBLANK(q2), 1, // New Qty Added
ISBLANK(q1) && NOT ISBLANK(q2), 2, // Qty Removed
q1 = q2, 3, // Qty Match
4 // Qty Don't Match
)
Once these numeric measures are created, you can aggregate them across the dataset to show meaningful counts in visuals. For example, you can create a measure to count how many times the dates match between the two tables:
Count of Dates Match =
CALCULATE(
COUNTROWS('Raw_Data 1'),
[Date Comparison Numeric] = 3
)
Similarly, create a measure to count how many times the quantities match:
Count of Qty Match =
CALCULATE(
COUNTROWS('Raw_Data 1'),
[Qty Comparison Numeric] = 3
)
You can also create measures to count other outcomes, such as new dates added or dates removed. The aggregation logic can be extended further by calculating percentages. For example, to calculate the percentage of matching dates:
Date Match Percentage =
DIVIDE(
[Count of Dates Match],
COUNTROWS('Raw_Data 1'),
0
)
If you want to visualize the different comparison outcomes in a bar chart, create a measure that returns the category label based on the numeric value from your comparison measure. For example, to categorize the date comparison results:
Date Comparison Categories =
SWITCH(
TRUE(),
[Date Comparison Numeric] = 1, "New Date Added",
[Date Comparison Numeric] = 2, "Date Removed",
[Date Comparison Numeric] = 3, "Dates Match",
[Date Comparison Numeric] = 4, "Dates Don't Match",
"No Date Present"
)
This measure can be used as a legend or axis in a bar chart to display the distribution of comparison outcomes across the dataset.
By using this approach, you retain the row-level logic in your comparisons while enabling meaningful aggregation for summary visuals. You can count the occurrences of each comparison outcome and visualize them effectively in cards, bar charts, and other visuals. This ensures that your comparison logic is not lost when you switch from a table visual to summary visuals, providing a more comprehensive view of your data comparisons.
Best regards,
