Forum Discussion

arunbyc's avatar
arunbyc
Helper III
1 year ago
Solved

Probably HASONEVALUE in my code causing unwanted effect? How do I fix this?

My following code displays the description of either (a) work or (b) Materials used in a project depending on whether the record is of type "time" or "materials". The column "lookup_id" in this table is linked to the work_dim and Materials_dim dimension tables, and the descriptions are obtained from the related dimension tables,  Description is displayed on individual records for each project. This is working fine.

 

Problem:

 

In the Matrix, these detailed records appear under each Project ID. Records at this rolled-up level (i.e. Project Level) should display total hours and total quantity of materials etc. but no descriptions.

 

But probably because I am using "HASONEVALUE( ) in my code, whenever there is only one record for a project, the description of the detailed (child) record from the related dimension tab;e is also displayed. For projects that have multiple time and Material records, the description is blank, as intended. I want the description to be blank at the Project level no matter how many records are there under a project. How can I fix this?

 

Code for the measure:

Description =
var x_id =  SELECTEDVALUE(Time_and_Material[lookup_id])
var display_Value = if (
                        HASONEVALUE(Time_and_Material[lookup_id]) &&
                        SELECTEDVALUE(Time_and_Material[lookup_id][type]) = "time" ,
                                calculate(SELECTEDVALUE(work_dim[description]),work_dim[id] = x_id),
                                calculate(SELECTEDVALUE(material_dim[description]),material_dim[id] = x_id)
                        )
    RETURN display_Value
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi arunbyc ,

    I create three tables as you mentioned.


    Then I think you can change the DAX code and you can try the codes below.

    Description = 
VAR x_id =
    SELECTEDVALUE ( Time_and_Material[lookup_id] )
VAR display_Value =
    IF (
        HASONEVALUE ( Time_and_Material[lookup_id] )
            && SELECTEDVALUE ( Time_and_Material[type] ) = "time",
        CALCULATE ( SELECTEDVALUE ( work_dim[description] ), work_dim[id] = x_id ),
        CALCULATE (
            SELECTEDVALUE ( material_dim[description] ),
            material_dim[id] = x_id
        )
    )
RETURN
    IF ( ISINSCOPE ( Time_and_Material[ProjectID] ), display_Value, BLANK () )

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi arunbyc ,

    I create three tables as you mentioned.


    Then I think you can change the DAX code and you can try the codes below.

    Description = 
VAR x_id =
    SELECTEDVALUE ( Time_and_Material[lookup_id] )
VAR display_Value =
    IF (
        HASONEVALUE ( Time_and_Material[lookup_id] )
            && SELECTEDVALUE ( Time_and_Material[type] ) = "time",
        CALCULATE ( SELECTEDVALUE ( work_dim[description] ), work_dim[id] = x_id ),
        CALCULATE (
            SELECTEDVALUE ( material_dim[description] ),
            material_dim[id] = x_id
        )
    )
RETURN
    IF ( ISINSCOPE ( Time_and_Material[ProjectID] ), display_Value, BLANK () )

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.