Forum Discussion

alvin199's avatar
alvin199
Helper III
4 years ago
Solved

Question on Relate and RelatedTable function

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
),
"")
 
 
  • v-yingjl's avatar
    v-yingjl
    4 years ago

    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.

5 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    4 years ago

    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's avatar
      alvin199
      Helper III
      4 years ago

      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's avatar
        Fowmy
        Super User
        4 years ago

        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: