Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I want to create a measure to get the below result
Measure = If Data Source (Serial No) & Amount Matches [tables] Table 1 (Serial No) & Amount or Table 2 (Serial No) & Amount or Table 3 (Serial No) & Amount or Table 4 (Serial No) & Amount return “ok” else “Error”
Then calculate the Amount difference and show in the last column
Result Display
SERIAL NO | AMOUNT | STATUS | AMOUNT DIFFERENCE |
9090939560 | 1985 | OK | 0 |
9090939561 | 1985 | OK | 0 |
9090939562 | 1985 | OK | 0 |
9090939563 | 1985 | OK | 0 |
9090939564 | 4755 | OK | 0 |
9090939565 | 1170 | Error | 170 |
9090939566 | 1085 | OK | 0 |
9090939567 | 1855 | Error | 5 |
9090939570 | 2595 | OK | 0 |
Please find below the sample if this helps.
Thanks
Gaurav
Solved! Go to Solution.
One of the ways is to use these measures (I assume your tables are connected via Serial No column):
#check =
VAR _t = UNION('Table 1','Table 2','Table 3','Table 4')
VAR amt = SUMX(_t, [AMOUNT])
VAR dsAmt = SUM('Data Source'[AMOUNT])
RETURN
IF(dsAmt = amt, "OK", "Error")
#diff =
VAR _t = UNION('Table 1','Table 2','Table 3','Table 4')
VAR amt = SUMX(_t, [AMOUNT])
VAR dsAmt = SUM('Data Source'[AMOUNT])
RETURN
IF([#check] = "Error", amt - dsAmt, 0)
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Please, check data types of the SN column in all tables. It should be whole number.
Here I used another table without one of the values to make sure it works:
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Hi @gaurav-narchal ,
Do I understand correctly that Serial No from Data Source table can be met only at one of the tables?
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
One of the ways is to use these measures (I assume your tables are connected via Serial No column):
#check =
VAR _t = UNION('Table 1','Table 2','Table 3','Table 4')
VAR amt = SUMX(_t, [AMOUNT])
VAR dsAmt = SUM('Data Source'[AMOUNT])
RETURN
IF(dsAmt = amt, "OK", "Error")
#diff =
VAR _t = UNION('Table 1','Table 2','Table 3','Table 4')
VAR amt = SUMX(_t, [AMOUNT])
VAR dsAmt = SUM('Data Source'[AMOUNT])
RETURN
IF([#check] = "Error", amt - dsAmt, 0)
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Hi @ERD
I'm almost there. I also need to validate if the serial number does exist in the Data Source table and Table 1 , 2 , 3 and 4. How can i validate this through measure?
Measure = If Data Source (Serial No) Matches [tables] Table 1 ,2 3 & 4 (Serial No) return “ok” else “Error”
#check =
VAR _t = UNION('Table 1','Table 2','Table 3','Table 4')
VAR amt = SUMX(_t, [AMOUNT])
VAR dsAmt = SUM('Data Source'[AMOUNT])
RETURN
IF(dsAmt = amt, "OK", "Error")
Thanks
You can use this measure:
#checkIfExists =
VAR currentSN = SELECTEDVALUE('Data Source'[SERIAL NO])
VAR _t =
UNION (
DISTINCT ( 'Table 1'[SERIAL NO] ),
DISTINCT ( 'Table 2'[SERIAL NO] ),
DISTINCT ( 'Table 3'[SERIAL NO] ),
DISTINCT ( 'Table 4'[SERIAL NO] )
)
RETURN
IF(currentSN IN _t, "OK", "Error")
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Please, check data types of the SN column in all tables. It should be whole number.
Here I used another table without one of the values to make sure it works:
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Hi @ERD - Some of the serial numbers are only aplhabets (JKHTGBLR) or blank how can i ignore them through the same measure.
Thank you for all your help.
It doesn't metter which type - Whole number or Text. The idea is that all the SN columns are of the SAME type.
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
82 | |
74 | |
66 |
User | Count |
---|---|
121 | |
105 | |
102 | |
82 | |
72 |