Question on Relate and RelatedTable function

[alvin199]

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

),

"")

[Fowmy]

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

[alvin199]

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?

[Fowmy]

@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:

[alvin199]

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

[v-yingjl · Accepted solution]

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.