Forum Discussion
Question on Relate and RelatedTable function
Hi alvin199 ,
In this case, you can try to use Hasonefilter instead of Hasonevalue for the column in your picture:
Like this:
Measure = HASONEFILTER('Table'[City])You can refer the difference between Hasonevalue and Hasonefilter:
HASONEFILTER vs. HASONEVALUE vs. ISFILTERED
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
alvin199
I believe the Total Revenue is a Column. Try the following modified formula. You need to use CALCULATE to activate the relationship between the tables.
Selected Product Category =
IF (
HASONEVALUE ( 'Store Lookup'[store_id] ),
CONCATENATEX (
VALUES ( 'Product Lookup'[product_category] ),
'Product Lookup'[product_category] & ": "
& CALCULATE ( SUM ( 'Sales by Store'[Total Revenue] ) ),
-- 'Product Lookup'[product_category],
", ",
'Product Lookup'[product_category], ASC
),
""
)
Hi Fowmy , thanks for reply.
Yes, Total Revenue is a Column in Sales by Store table.The DAX works fine. I still do not understand why we do not need to use RELATE or RELATEDTABLE in here even both the table has active relationship.
Besides, when I use it to as a filter context for store_id and store_city (from Store Lookup table), it display the total similar to 2 rows together (store_id and store_city) for each type of the product category.
How can I empty the store_id row and only the store_city has the value for each product category?alvin199
You can use the RELATEDTABLE function when you the Dimension level and need to fetch data from the FAct table, as it returns a table you need to do the calculation.
To get rid of the two-line in the matrix, get rid of the Steped Layout:I am referring to the column Selected Product Category (Concatenatex, Ass, Improved). I would like to empty all the store_id rows across the Selected Product Category column.
I am thinking that I need to add an expression on the IF(HasOneValue).
