Forum Discussion

alicek's avatar
alicek
Icon for Helper III rankHelper III
3 years ago
Solved

Use SELECTEDVALUE to return 1 value from 1 column based on a shared key field to another table?

Hi all,    We have one key field called DEPARTMENT across three tables: a reference table and two different fact tables. The reference table is just a list of each unique DEPARTMENT, plus one row ...
Need Help
  • danextian's avatar
    danextian
    3 years ago

    Hi alicek 

    The first formula below will match what is in Visual2. But I am curious why there is no DPT1 in your expected result when you selected DPT1 and for that is another formula.

    Percent = 
VAR __DPT =
    SELECTEDVALUE ( reference[DPT] )
RETURN
    IF (
        SELECTEDVALUE ( table2[DPT] ) = "CIT",
        CALCULATE ( MAX ( table1[Percent] ), FILTER ( table1, table1[DPT] = __DPT ) )
    )

    Percent2 = 
VAR __DPT =
    SELECTEDVALUE ( reference[DPT] )
RETURN
    IF (
        SELECTEDVALUE ( table2[DPT] ) = "CIT",
        CALCULATE ( MAX ( table1[Percent] ), FILTER ( table1, table1[DPT] = __DPT ) ),
        CALCULATE (
            MAX ( table1[Percent] ),
            FILTER ( table1, table1[DPT] = __DPT ),
            FILTER ( table2, table2[DPT] = __DPT )
        )
    )

     

     

danextian's avatar
danextian
Icon for Super User rankSuper User
3 years ago

Hi alicek ,

 

Your desired end result is confusing as projects 3 and 4 are both dept2 in your fact table#2 but dept1 in your end result. That aside, please try this:

Percent = 
CALCULATE (
    MAX ( 'Fact1'[Percent] ),
    FILTER ( 'Fact1', 'Fact1'[DPT] = SELECTEDVALUE ( DPT[DPT] ) )
)

If those projects under DPT2 should have 10%, you can simply create a relationship between tables Fact1 and 2 or use the measure below:

Percent2 = 
CALCULATE (
    MAX ( 'Fact1'[Percent] ),
    FILTER ( 'Fact1', 'Fact1'[DPT] = SELECTEDVALUE ( Fact2[DPT] ) )
)

 

 

  • alicek's avatar
    alicek
    Icon for Helper III rankHelper III
    3 years ago

    Thank you!! It is indeed what the outcome we wanted was. I was trying to simplify it, but basically in Fact Table #2, the projects are all associated with a DPT called "CIT". But CIT projects are actually charged out to every department based on the some percentage (see: Fact Table#1, which represents the percent each department is charged if it is a CIT project).

    So, in the final table, we want to show all projects that are techincally CIT deparmtent projects -- but show the percent that their total hours should be billed to whatever department is chosen in the slicer. I don't know if that changes your answer at all!! So we think your answer doesn't quite get us there!!

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      3 years ago

      Hi,

      There is missing information.  I do not see any reference to CIT in the 3 tables that you shared in your first post.

      • alicek's avatar
        alicek
        Icon for Helper III rankHelper III
        3 years ago

        Hi Ashish_Mathur and danextian ! Thank you so much for both of your replies. I apologize - I thought I was helping by simplifying it, but I see that caused confusion. I've re-written to go into more detail. Please let me know if you have questions. The original description was technically correct, but was confusing because it appeared to be an error to readers.

    • danextian's avatar
      danextian
      Icon for Super User rankSuper User
      3 years ago

      Please provide a sample data that is a representation of your use case and with that sample data, the result you expect.