Forum Discussion

GuestUser's avatar
GuestUser
Icon for Helper V rankHelper V
6 years ago
Solved

Report Format Help

Hi ,   I have 4 measures   Actual Count-MTD -- From one fact table Actual Count-YTD Budget Count _MTD -- From other fact table Budget Count-YTD   Need the report in below format            ...
Need Help
  • mwegener's avatar
    mwegener
    6 years ago

    For sure. 

    PBIX

     

GuestUser's avatar
GuestUser
Icon for Helper V rankHelper V
6 years ago

Thanks v-alq-msft for your reply

 

But in the report format, on left hand side - i do not need the dates , I need a hardcoded Value (which does not come from any table) -- "Actual" and "Budget"

 

like

                 2019

                 MTD       YTD

Actual

Budget

 

Can you pls suggest

mwegener's avatar
mwegener
Icon for Most Valuable Professional rankMost Valuable Professional
6 years ago

Hi GuestUser ,

 

check this PBIX

 

  • GuestUser's avatar
    GuestUser
    Icon for Helper V rankHelper V
    6 years ago
    Thanks mwegener

    But since I am using connect live (ssas tabular) I am not able to use selected value function
    Is there any other formula which can help?
    • GuestUser's avatar
      GuestUser
      Icon for Helper V rankHelper V
      6 years ago

      Hi mwegener ,

       

      Tried this by replacing it with hasonevalue and somewhat working

       

      Measure1 =
      SWITCH (
      IF ( HASONEVALUE ( MeasureType[MeasureType] ), VALUES ( MeasureType[MeasureType] ) ),
      "Actual", SWITCH (
      IF ( HASONEVALUE ( TimeType[TimeType] ), VALUES (TimeType[TimeType] ) ),
      "MTD", [Actual Count-MTD],
      "YTD", [Actual Count-YTD],
      BLANK ()
      ),
      "Budget", SWITCH (
      IF ( HASONEVALUE ( TimeType[TimeType] ), VALUES (TimeType[TimeType] ) ),
      "MTD", [Budget Count-MTD],
      "YTD", [Budget Count-YTD],
      BLANK ()
      ),
      BLANK ()
      )

       

      But when I drag the fiscal year and month on top of TimeType in Column Section (Matrix View) , the report becomes blank.

       

      Any suggestions pls?

      • mwegener's avatar
        mwegener
        Icon for Most Valuable Professional rankMost Valuable Professional
        6 years ago

        Hi GuestUser ,

         

        try this.

         

        Measure =
IF (
    HASONEVALUE ( MeasureType[MeasureType] ) && HASONEVALUE ( TimeType[TimeType] ),
    SWITCH (
        VALUES ( MeasureType[MeasureType] ),
        "Actual", SWITCH (
            VALUES ( TimeType[TimeType] ),
            "MTD", [Actual Count-MTD],
            "YTD", [Actual Count-YTD],
            BLANK ()
        ),
        "Budget", SWITCH (
            VALUES ( TimeType[TimeType] ),
            "MTD", [Budget Count-MTD],
            "YTD", [Budget Count-YTD],
            BLANK ()
        ),
        BLANK ()
    ),
    BLANK ()
)