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 am working on creating a model using an Excel file with 3 different sheets. This is to help a store price their products. Sheet 1 contains 3 columns, Location, Price, and When it was updated (it is either within 30 days, or Not within 30 days) Sheet 2 contains 2 columns, locations (Similar to sheet one but they are from another company) and the price they sell the product for. Sheet 3 contains similar locations and the estimated price of the product. My goal is to create a query where We select a location, and it will take the price in order of the rules and show what sheet it is from. (If a price is “Null” or “Blank” it will move to the next rule). I only want it showing me one price.
The Hierarchy of which price I want it to show are as followed:
Sheet 1 Price (and to tell me if it was updated within 30 days or not) à if this price is unavailable, it will show me the price for the same location from sheet 2. à if this is unavailable it will give me the price for sheet 3.
If possible, I would like to add another column to my results being, the average of all of them weather there is 1 price available or all 3.
HOW CAN I DO THIS???
Solved! Go to Solution.
Hi @ddb300 ,
I created some data:
Table1:
Table2:
Table3:
Here are the steps you can follow:
Will it be updated within 30 days:
Create measure.
measure_update =
var _update=SELECTEDVALUE('Table1'[Location])
return
CALCULATE(MAX('Table1'[Update]),FILTER(ALL('Table1'),'Table1'[Location]=_update))
Price hierarchy:
Create measure.
Price hierarchy =
var _location=SELECTEDVALUE('Table1'[Location])
var _price1=CALCULATE(MAX('Table1'[Price]),FILTER(ALL('Table1'),'Table1'[Location]=_location))
var _price2=CALCULATE(MAX('Table2'[Price]),FILTER(ALL('Table2'),'Table2'[Location]=_location))
var _price3=CALCULATE(MAX('Table3'[Price]),FILTER(ALL('Table3'),'Table3'[Location]=_location))
return
IF(
_price1<>"NULL",_price1,
IF(
_price2<>"NULL",_price2,
IF(
_price3<>"NULL",_price3,BLANK())))
When the prices in Table 1 and Table 2 are both NULL, the prices in Table 3 will be displayed directly:
Price available:
Create a calculated column in each table:
table1_count =
COUNTX(FILTER('Table1','Table1'[Price]<>"NULL"&&'Table1'[Location]=EARLIER('Table1'[Location])),[Price])
table2_count =
COUNTX(FILTER('Table2','Table2'[Price]<>"NULL"&&'Table2'[Location]=EARLIER('Table2'[Location])),[Price])
table3_count =
COUNTX(FILTER('Table3','Table3'[Price]<>"NULL"&&'Table3'[Location]=EARLIER('Table3'[Location])),[Price])
Create measure.
price available =
var _location=SELECTEDVALUE('Table1'[Location])
var _price1=CALCULATE(SUM('Table1'[table1_count]),FILTER(ALL('Table1'),'Table1'[Location]=_location))
var _price2=CALCULATE(SUM('Table2'[table2_count]),FILTER(ALL('Table2'),'Table2'[Location]=_location))
var _price3=CALCULATE(SUM('Table3'[table3_count]),FILTER(ALL('Table3'),'Table3'[Location]=_location))
return
_price1+_price2+_price3
Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @ddb300 ,
I created some data:
Table1:
Table2:
Table3:
Here are the steps you can follow:
Will it be updated within 30 days:
Create measure.
measure_update =
var _update=SELECTEDVALUE('Table1'[Location])
return
CALCULATE(MAX('Table1'[Update]),FILTER(ALL('Table1'),'Table1'[Location]=_update))
Price hierarchy:
Create measure.
Price hierarchy =
var _location=SELECTEDVALUE('Table1'[Location])
var _price1=CALCULATE(MAX('Table1'[Price]),FILTER(ALL('Table1'),'Table1'[Location]=_location))
var _price2=CALCULATE(MAX('Table2'[Price]),FILTER(ALL('Table2'),'Table2'[Location]=_location))
var _price3=CALCULATE(MAX('Table3'[Price]),FILTER(ALL('Table3'),'Table3'[Location]=_location))
return
IF(
_price1<>"NULL",_price1,
IF(
_price2<>"NULL",_price2,
IF(
_price3<>"NULL",_price3,BLANK())))
When the prices in Table 1 and Table 2 are both NULL, the prices in Table 3 will be displayed directly:
Price available:
Create a calculated column in each table:
table1_count =
COUNTX(FILTER('Table1','Table1'[Price]<>"NULL"&&'Table1'[Location]=EARLIER('Table1'[Location])),[Price])
table2_count =
COUNTX(FILTER('Table2','Table2'[Price]<>"NULL"&&'Table2'[Location]=EARLIER('Table2'[Location])),[Price])
table3_count =
COUNTX(FILTER('Table3','Table3'[Price]<>"NULL"&&'Table3'[Location]=EARLIER('Table3'[Location])),[Price])
Create measure.
price available =
var _location=SELECTEDVALUE('Table1'[Location])
var _price1=CALCULATE(SUM('Table1'[table1_count]),FILTER(ALL('Table1'),'Table1'[Location]=_location))
var _price2=CALCULATE(SUM('Table2'[table2_count]),FILTER(ALL('Table2'),'Table2'[Location]=_location))
var _price3=CALCULATE(SUM('Table3'[table3_count]),FILTER(ALL('Table3'),'Table3'[Location]=_location))
return
_price1+_price2+_price3
Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
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 |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |