topic Re: Question on Relate and RelatedTable function in DAX Commands and Tips

Question on Relate and RelatedTable function

alvin199 — Sun, 17 Oct 2021 11:59:19 GMT

Hi,

I would like to know why I do not need to use RELATE OR RELATEDTABLE function on 'Sales by Store'[Total Revenue] or 'Sales by Store', respectively, but use either one only as I am using Store Lookup in the whole DAX? There are relationship between Sales by Store table and Product Lookup table.

Selected Product Category =

IF(

HASONEVALUE('Store Lookup'[store_id]

CONCATENATEX(

VALUES(

'Product Lookup'[product_category]

'Product Lookup'[product_category] & ": " & sum('Sales by Store'[Total Revenue]),

-- 'Product Lookup'[product_category],

", ",

'Product Lookup'[product_category],

ASC

"")

Re: Question on Relate and RelatedTable function

Fowmy — Sun, 17 Oct 2021 12:10:12 GMT

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 ), "" )

Re: Question on Relate and RelatedTable function

alvin199 — Sun, 17 Oct 2021 12:50:09 GMT

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?

Re: Question on Relate and RelatedTable function

Fowmy — Sun, 17 Oct 2021 13:18:26 GMT

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:

Re: Question on Relate and RelatedTable function

alvin199 — Sun, 17 Oct 2021 14:10:59 GMT

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).

Re: Question on Relate and RelatedTable function

v-yingjl — Wed, 20 Oct 2021 06:11:20 GMT

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.