Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
BLUF Question: Is there a way to count items that need to be summed for either Card if they are found in the array requardless of the date ranges selected in the slicer.
Just want to find the Substring within String and return a flag of 1 if it is found.
Background Data showing the expected totals for the quarter
Desired PO Dt (2022 Q1): 74
Pr Sub Dt (2022 Q1): 88
The highlighted dates will be of interest below.
Once the slicer is engaged only Card 1 shows the correct total for 2022 Q1, Card2 shows sum of zero
Card 1: 74
Card 2: 0
-the slicer allows the user to define the date range,
-the date selected array is a string of dates as defined by the slicer, getting all values from the Calendar table
The PO Flags are found in the array, flagged and displayed in Card 1 as a sum: 74
Formula: Desired PO Issue Selected Flag =
IF(ISBLANK(SEARCH([Desired PO Issue Gen_Date],[Dates Selected Array],1,BLANK())),0,1)
The PR Flags are not being found, not flagged but probably displayed correctly in Card 2 as a sum: 0
Formula: PR Submitted Selected Flag = IF(ISBLANK(SEARCH([PR Submitted Gen_Date],[Dates Selected Array],1,BLANK())),0,1)
Summary when no filter is on, the correct totals in the first set of pictures is correct (74 & 88).
Once a filter is applied Card 1 show correct sum but even though we mortals can seem some of the dates in PR Sub Date, these date strings when using the formulas can't find the string noted in the green circles on the Array Card and PR Date.
There is a relationship between the table of source of these dates and the Calendar Date Table based upon the PO Date. When this relationship between the table is changed so that it relates to the PR Date, then Card 1 goes to zero and Card2 is correctly populated at 88 for the quarter.
Question: Is there a way to count items that need to be summed for either Card if they are found in the array requardless of the date ranges selected in the slicer.
Just want to find the Substring within String and return a flag of 1 if it is found.
Thanks in Advance.
Solved! Go to Solution.
work around was to create a second table with the source data columns, do the build the calculated fields and creat an active relationship between the new table and the data table based upon the other PR Date. Using the flag where it looked for the substring in the string (aka the array), I then put the flag colum into the card and summed the values.
Works like a charm albeit a little dirty in the model.
Will wait to see response about the {p2 Measure] before accepting my solution.
work around was to create a second table with the source data columns, do the build the calculated fields and creat an active relationship between the new table and the data table based upon the other PR Date. Using the flag where it looked for the substring in the string (aka the array), I then put the flag colum into the card and summed the values.
Works like a charm albeit a little dirty in the model.
Will wait to see response about the {p2 Measure] before accepting my solution.
I got this working with another method that I will explain below but am interested to explore your method.
I tried the Inactive Realtionships (there are are still there) and watched a video on USERELATIONSHIP.
Not understanding what you mean by the [P2 Measure], is this my formula looking into the array for the value?
Thanks
Rob
@rdnewcomb Sorry for the late reply, the [P2 measure] is just your formula to calculate the measure you want for P2, using the active relationship, then you use the USERELATIONSHIP function to change which relationship it uses to calculate that same formula.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@rdnewcomb You need to create an inactive relationship between the PR Date and then use the USERELATIONSHIP to create the count measure:
CALCULATE ( [P2 Measure] , USERELATIONSHIP ( fact[PR DAte], Calendar[date]) )
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |