Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi I created a running total chart using measured from DAX and connecting them to a master calendar table query. Initially, these formulas worked fine (picture 1) when I set them up as a single directional relationships in the model view. But since I want to use the filters, I decided to change them to bi-directional so that it reflects appropriately, but then all my target lines go wonky (picture2). Is there something I am missing, or can do better to set this feature up?
what was the rationale for the bidirectional link?
Did you use the Running Total Quick Measure?
For the first picture, if i click on the filters, only the top two (straight) target lines moved but not the other running total lines. And upon research online, I read this would be successful if my relationship was bidirectional.
As for running total, I did not use quick measure. I created my own measures. Example:
RTYTD =
VAR MaxDate = MAX('Calendar Table'[Dates])
RETURN
CALCULATE(SUM('Table1'[Potential Cost Savings]), FILTER(ALLNOBLANKROW('Table1'), 'Table1'[Actual Date] <= MaxDate && 'Table1'[Actual Date] <> BLANK()))
I figured out most of the solution in the last few hours, where if i add allcrossfiltered bit to the measure, the lines are corrected to what it shouldve been line in the picture 1. But if i clear filters, the two target lines (green & blue) still show wonky like picture 2. The problem is I dont know how to add the cross-filter logic since they are not really measures, they are just static numbers being pulled from power query that I created with target amt.
you can make them measures with a fake aggregation like MIN() or MAX() or AVERAGE()
User | Count |
---|---|
14 | |
9 | |
7 | |
7 | |
6 |
User | Count |
---|---|
21 | |
11 | |
10 | |
10 | |
8 |